let express = require("express")();
let url = require('url');
let fs = require('fs');
let mysql = require('mysql');
let expressStatic = require('express-static');
const { request, response } = require("express");

var sql = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '000000',
    database: 'school',
    timezone: "08:00"
});

sql.connect();
const port = 80;

let count = 0;

express.get("/", (request, response) => {
    fs.readFile("deng.html", (error, data) => {
        if (error) {
            response.end("error");
            console.log("找不到首页文件了")
        } else {
            response.end(data);
        }
    })
})

express.get("/getData", (request, response) => {
    let callback = url.parse(request.url, true).query.callback;
    sql.query('SELECT * FROM student ORDER BY id ASC', (error, data) => {
        // console.log(data)
        // console.log(error)
        if (error) {
            response.end("database error");
            console.log("数据库查找失败")
        } else {
            response.end(`${callback}(${JSON.stringify(data)})`)
        }
    })
})
express.get("/shanData", (request, response) => {
    let id = url.parse(request.url, true).query.id;
    // let callback = url.parse(request.url, true).query.callback;
    sql.query(`DELETE FROM student WHERE id=${id}`, (error, data) => {
        // if (id === undefined) {
        //     response.end("请发送需删除的ID");
        //     console.log("数据库删除失败")
        // } else {
        //     response.end(`${callback}`)
        // }
        if (error) {
            response.end("请发送需删除的ID");
            console.log("数据库删除失败")
        } else {
            response.end("成功")
        }
    })
})
express.get("/shanScore", (request, response) => {
    let id = url.parse(request.url, true).query.id;
    let kemu = url.parse(request.url, true).query.kemu;
    let fenshu = url.parse(request.url, true).query.chengji;
    let shijian = url.parse(request.url, true).query.shijian;

    sql.query(`DELETE FROM score WHERE studentid="${id}" AND kemu="${kemu}" AND fenshu="${fenshu}" AND time="${shijian}"`, (error, data) => {
        if (error) {
            response.end("请发送需删除的ID");
            console.log("数据库删除失败")
        } else {
            response.end("成功")
        }
    })
})
express.get("/addData", (request, response) => {
    let name = url.parse(request.url, true).query.name
    let age = url.parse(request.url, true).query.age
    let city = url.parse(request.url, true).query.city
    let sex = url.parse(request.url, true).query.sex
    sql.query(`INSERT INTO student (name,age,city,sex) VALUES ("${name}","${age}","${city}","${sex}")`, (error, data) => {
        if (error) {
            response.end("数据库删除失败");
            console.log("数据库删除失败")
        } else {
            response.end("成功")
        }
    })
})
express.get("/addScore", (request, response) => {
    let id = url.parse(request.url, true).query.id
    let kemu = url.parse(request.url, true).query.kemu
    let fenshu = url.parse(request.url, true).query.fenshu
    let shijian = url.parse(request.url, true).query.shijian
    sql.query(`INSERT INTO score (studentid,kemu,fenshu,time) VALUES ("${id}","${kemu}","${fenshu}","${shijian}")`, (error, data) => {
        if (error) {
            response.end("数据库添加失败");
            console.log("数据库添加失败")
        } else {
            response.end("成功")
        }
    })
})
express.get("/gaiData", (request, response) => {
    let id = url.parse(request.url, true).query.id
    let name = url.parse(request.url, true).query.name
    let age = url.parse(request.url, true).query.age
    let city = url.parse(request.url, true).query.city
    let sex = url.parse(request.url, true).query.sex
    sql.query(`UPDATE student SET name='${name}',sex='${sex}',age='${age}',city='${city}' WHERE id=${id}`, (error, data) => {
        if (error) {
            response.end("数据库更改失败");
            console.log("数据库更改失败")
        } else {
            response.end("成功")
        }
    })
})
express.get("/zhuce", (request, response) => {
    let zhang = url.parse(request.url, true).query.zhang
    let mima = url.parse(request.url, true).query.mima
    let name = url.parse(request.url, true).query.name
    sql.query(`INSERT INTO users (user,password,name) VALUES ("${zhang}","${mima}","${name}")`, (error, data) => {
        if (error) {
            response.end("用户名已注册");
            console.log("注册失败")
        } else {
            response.end("成功")
        }
    })
})
express.get("/denglu", (request, response) => {
    let zhang = url.parse(request.url, true).query.zhang
    let mima = url.parse(request.url, true).query.mima
    sql.query(`SELECT * FROM users WHERE user="${zhang}"`, (error, data) => {
        console.log(JSON.stringify(data))
        if (JSON.stringify(data) != "[]") {
            let a = data[0]
            if (a.password === mima) {
                let arr = "成功" + a.name
                response.end(`${arr}`);
            } else {
                response.end("密码错误");
            }

            console.log(a.user, a.password, a.name)
        } else {
            response.end("账号不存在")
        }
    })
})
express.get("/getScore", (request, response) => {
    let name = url.parse(request.url, true).query.name
    let date = url.parse(request.url, true).query.date
    let kemu = url.parse(request.url, true).query.kemu
    if (kemu == "全部科目") {
        kemu = ""
    }
    if (!name && !date && !kemu) {
        sql.query(`select student.id,student.name,score.kemu,score.fenshu,score.time from student,score WHERE student.id=score.studentid`, (error, data) => {
            response.end(JSON.stringify(data));
        })
    }
    if (name && date && kemu) {

        sql.query(`select student.id,student.name,score.kemu,score.fenshu,score.time from student,score WHERE student.id=score.studentid AND score.time="${date}" AND student.name="${name}" AND score.kemu="${kemu}"`, (error, data) => {
            response.end(JSON.stringify(data));
        })


    }
    if (name && date) {
        sql.query(`select student.id,student.name,score.kemu,score.fenshu,score.time from student,score WHERE student.id=score.studentid AND score.time="${date}" AND student.name="${name}"`, (error, data) => {
            response.end(JSON.stringify(data));
        })
    }
    if (kemu && date) {
        sql.query(`select student.id,student.name,score.kemu,score.fenshu,score.time from student,score WHERE student.id=score.studentid AND score.time="${date}" AND score.kemu="${kemu}"`, (error, data) => {
            response.end(JSON.stringify(data));
        })
    }
    if (name && kemu) {
        sql.query(`select student.id,student.name,score.kemu,score.fenshu,score.time from student,score WHERE student.id=score.studentid AND score.kemu="${kemu}" AND student.name="${name}"`, (error, data) => {
            response.end(JSON.stringify(data));
        })
    }
    if (name) {
        sql.query(`select student.id,student.name,score.kemu,score.fenshu,score.time from student,score WHERE student.id=score.studentid AND student.name="${name}"`, (error, data) => {
            response.end(JSON.stringify(data));
        })
    }
    if (date) {
        sql.query(`select student.id,student.name,score.kemu,score.fenshu,score.time from student,score WHERE student.id=score.studentid AND score.time="${date}"`, (error, data) => {
            response.end(JSON.stringify(data));
        })
    }
    if (kemu) {
        sql.query(`select student.id,student.name,score.kemu,score.fenshu,score.time from student,score WHERE student.id=score.studentid AND score.kemu="${kemu}"`, (error, data) => {
            response.end(JSON.stringify(data));
        })
    }
})
express.get("/getScore1", (request, response) => {
    let name = url.parse(request.url, true).query.name
    let id = url.parse(request.url, true).query.id

    if (name) {
        sql.query(`select student.id,student.name,score.kemu,score.fenshu,score.time from student,score WHERE student.id=score.studentid AND student.name="${name}" AND student.id="${id}"`, (error, data) => {
            response.end(JSON.stringify(data));
        })
    }
})
express.get("/gaiScore", (request, response) => {
    let id = url.parse(request.url, true).query.id
    let kemu = url.parse(request.url, true).query.kemu
    let date = url.parse(request.url, true).query.date
    let fenshu = url.parse(request.url, true).query.fenshu


    sql.query(`UPDATE score SET fenshu='${fenshu}' WHERE studentid="${id}" AND time="${date}" AND kemu="${kemu}"`, (error, data) => {
        if (error) {
            response.end("数据库更改失败");
            console.log("数据库更改失败")
        } else {
            response.end("成功")
        }
    })

})

express.use(expressStatic(__dirname))
express.listen(port, () => {
    console.log(`server is running at ${port}`)
})